﻿using System;
using System.Data.SqlServerCe;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using SenseTimeTracker.Misc;

namespace SenseTimeTracker.BusinessLayer
{
	public class WorkingTime
	{
		public const string IdColumnName = "workingTimeId";
		public const string PersonIdColumnName = "personId";
		public const string ValidfromColumnName = "validFrom";
		public const string DailyWorkingTimeColumnName = "workingTime";
		public const string TableName = "WorkingTime";

		private Guid id = Guid.NewGuid();
		private Guid personId = Guid.Empty;
		private DateTime validFrom = DateTime.Now.Date;
		private TimeSpan dailyWorkingTime = Persistance.Instance.DefaultDailyWorkingTime;

		public Guid Id
		{
			get { return id; }
			set { id = value; }
		}

		public Guid PersonId
		{
			get { return personId; }
			set { personId = value; }
		}

		public DateTime ValidFrom
		{
			get { return validFrom.Date; }
			set { validFrom = value.Date; }
		}

		public TimeSpan DailyWorkingTime
		{
			get { return dailyWorkingTime; }
			set { dailyWorkingTime = value; }
		}

		public static List<WorkingTime> WorkingTimes
		{
			get
			{
				List<WorkingTime> workingTimes = new List<WorkingTime>();
				SqlCeCommand command = new SqlCeCommand(
						"SELECT " +
						IdColumnName + ", " +
						PersonIdColumnName + ", " +
						ValidfromColumnName + ", " +
						DailyWorkingTimeColumnName +
						" FROM " + TableName,
						DatabaseManager.DbConnection);
				bool shallOpen = DatabaseManager.DbConnection.State != System.Data.ConnectionState.Open;
				if (shallOpen)
				{
					DatabaseManager.DbConnection.Open();
				}

				SqlCeDataReader reader = command.ExecuteReader();
				while (reader.Read())
				{
					workingTimes.Add(new WorkingTime
					{
						Id = (Guid)reader[IdColumnName],
						PersonId = (Guid)reader[PersonIdColumnName],
						ValidFrom = (DateTime)reader[ValidfromColumnName],
						DailyWorkingTime = TimeSpan.FromHours((float)reader[DailyWorkingTimeColumnName]),
					});
				}

				if (shallOpen)
				{
					DatabaseManager.DbConnection.Close();
				}

				return workingTimes;
			}
		}

		public static void Clear(Guid personId)
		{
			SqlCeCommand command = new SqlCeCommand(
				"DELETE FROM " + TableName + " WHERE " + PersonIdColumnName + " = @" + PersonIdColumnName,
				DatabaseManager.DbConnection);
			command.Parameters.Add(PersonIdColumnName, personId);
			DatabaseManager.DbConnection.Open();
			command.ExecuteScalar();
			DatabaseManager.DbConnection.Close();
		}

		public void Insert()
		{
			SqlCeCommand command = new SqlCeCommand(
				"INSERT INTO " + TableName + " (" +
				IdColumnName + ", " +
				PersonIdColumnName + ", " +
				ValidfromColumnName + ", " +
				DailyWorkingTimeColumnName + ") " +
				"VALUES (" +
				"@" + IdColumnName + ", " +
				"@" + PersonIdColumnName + ", " +
				"@" + ValidfromColumnName + ", " +
				"@" + DailyWorkingTimeColumnName + ")",
				DatabaseManager.DbConnection);

			command.Parameters.Add(IdColumnName, id);
			command.Parameters.Add(PersonIdColumnName, personId);
			command.Parameters.Add(ValidfromColumnName, validFrom);
			command.Parameters.Add(DailyWorkingTimeColumnName, dailyWorkingTime.TotalHours);
			DatabaseManager.DbConnection.Open();
			command.ExecuteNonQuery();
			DatabaseManager.DbConnection.Close();
		}

		public void Update()
		{
			SqlCeCommand command = new SqlCeCommand(
				"UPDATE " + TableName + " SET " +
				ValidfromColumnName + " = @" + ValidfromColumnName + ", " +
				DailyWorkingTimeColumnName + " = @" + DailyWorkingTimeColumnName + " " +
				"WHERE " + IdColumnName + " = @" + IdColumnName,
				DatabaseManager.DbConnection);

			command.Parameters.Add(IdColumnName, id);
			command.Parameters.Add(ValidfromColumnName, validFrom);
			command.Parameters.Add(DailyWorkingTimeColumnName, dailyWorkingTime.TotalHours);
			DatabaseManager.DbConnection.Open();
			command.ExecuteNonQuery();
			DatabaseManager.DbConnection.Close();
		}

		//public void Persist()
		//{
		//  SqlCeCommand command = new SqlCeCommand(
		//    "SELECT " + IdColumnName + " FROM " + TableName + " WHERE " + IdColumnName + "=@" + IdColumnName,
		//    DatabaseManager.DbConnection);
		//  command.Parameters.Add(IdColumnName, id);
		//  DatabaseManager.DbConnection.Open();
		//  object result = command.ExecuteScalar();
		//  DatabaseManager.DbConnection.Close();
		//  if (result != null)
		//  {
		//    Update();
		//  }
		//  else
		//  {
		//    Insert();
		//  }
		//}
	}
}
